package publicity.desk.note
{
        import flash.filesystem.File;
        import flash.data.SQLConnection;
        import flash.data.SQLStatement;
        import flash.data.SQLResult;
	import flash.errors.SQLError;
        /**
         * Central Information Administration
	 * author James
         */
        public class CIA {

                /** db file name */
                private var FILE_NAME:String = "diary.db";
		/** */
		private var FILE_PATH:String = "/Simple Air Note/";

                private var _dbFile:File;
                private var _dbConnection:SQLConnection;
                private var _createStatement:SQLStatement;
                private var _insertStatement:SQLStatement;
                private var _deleteStatement:SQLStatement;
                private var _updateStatement:SQLStatement;
                private var _selectStatement:SQLStatement;

                public function CIA() {
                }

		/**
		 * get system user
		 */
		public function get OSUser():String {
		    var path:Array = File.userDirectory.url.split("/");
                    return path[path.length - 1] as String;
		}

                /**
                 * Test the db file exists or not
                 */
                public function get dbIsExist():Boolean {
                    var vFile:File = new File(File.documentsDirectory.url + FILE_PATH + FILE_NAME);
                    return vFile.exists;
                }

                /**
                 * create a db file
                 */
                public function createDb():void {
		    var dbPath:File = new File(File.documentsDirectory.url + FILE_PATH);
                    dbPath.createDirectory();
                    _dbFile = dbPath.resolvePath(FILE_NAME);
                }

		/**
		 * get db file
		 */
		public function getDbFile():void {
		    var dbPath:File = new File(File.documentsDirectory.url + FILE_PATH);
		    _dbFile = dbPath.resolvePath(FILE_NAME);
		}

                /**
                 * create connection
                 */
                public function createConnection():void {
                    _dbConnection = new SQLConnection();
                    try {
                        _dbConnection.open(_dbFile);
			trace("db open success.");
                    } catch(error:SQLError) {
                        trace("Error on openning db.");
                    }
                }

                /**
                 * create table user
                 */
                public function createUserT():void {
                    _createStatement = new SQLStatement();
                    _createStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "CREATE    TABLE IF NOT EXISTS USER (";
                    sql += "USERID    INTEGER PRIMARY KEY AUTOINCREMENT,";
                    sql += "USERNAME  TEXT,";
                    sql += "PASSWORD  TEXT,";
                    sql += "USERLEVEL NUMBER CHECK (userlevel <= 9) DEFAULT 0,";
                    sql += "COMMENT   TEXT";
                    sql += ")";
                    _createStatement.text = sql;
                    try {
                        _createStatement.execute();
			trace("table user create success.");
                    } catch(error:SQLError) {
                        trace("Error on creating table user.");
                    }
                }

                /**
                 * create table record
                 */
                public function createRecordT():void {
                    _createStatement = new SQLStatement();
                    _createStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "CREATE   TABLE IF NOT EXISTS RECORD (";
                    sql += "RECORDID INTEGER PRIMARY KEY AUTOINCREMENT,";
                    sql += "DAY      TEXT,";
                    sql += "CONTENT  TEXT";
                    sql += ")";
                    _createStatement.text = sql;
                    try {
                        _createStatement.execute();
			trace("table record create success.");
                    } catch(error:SQLError) {
                        trace("Error on creating table record.");
			trace(error.message);
			trace(error.details);
                    }
                }

                /**
                 * add user account
                 */
                public function addUser(username:String, pass:String, lev:int=0, cmt:String=null):void {
                    _insertStatement = new SQLStatement();
                    _insertStatement.sqlConnection = _dbConnection;
		    var cmtWith:String = "'" + cmt + "'";
		    cmt = (cmt == null) ? cmt : cmtWith;
                    var sql:String = "";
                    sql += "INSERT INTO USER (USERNAME, PASSWORD, USERLEVEL, COMMENT) ";
                    sql += "VALUES ('" + username + "', '" + pass + "', " + lev + ", " + cmt + ")";
		    trace(sql);
                    _insertStatement.text = sql;
                    try { 
                        // execute the statement 
                        _insertStatement.execute();
			trace("user insert success.");
                    } catch (error:SQLError) { 
                        trace("Error on inserting user.");
			trace(error.message);
			trace(error.details);
                    }
                }

                /**
                 * add record
                 */
                public function addRecord(day:String, content:String):void {
                    _insertStatement = new SQLStatement();
                    _insertStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "INSERT INTO RECORD (DAY, CONTENT) ";
                    sql += "VALUES ('" + day + "', '" + content + "')";
		    trace(sql);
                    _insertStatement.text = sql;
                    try { 
                        // execute the statement 
                        _insertStatement.execute();
			trace("record insert success.");
                    } catch (error:SQLError) { 
                        trace("Error on inserting record.");
			trace(error.message);
			trace(error.details);
                    }
                }

                /**
                 * get user by username
                 */
                public function getUserByName(username:String):Object {
                    _selectStatement = new SQLStatement();
                    _selectStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "SELECT USERID, USERNAME, PASSWORD, USERLEVEL, COMMENT FROM USER ";
                    sql += "WHERE USERNAME = '" + username + "'";
		    trace(sql);
                    _selectStatement.text = sql;
                    var result:SQLResult = new SQLResult();
                    try {
                        _selectStatement.execute();
                        result = _selectStatement.getResult();
                    } catch(error:SQLError) {
                        trace("Error on getting user by username.");
			trace(error.message);
			trace(error.details);
                    }
                    return (result.data[0] == null)? null : result.data[0];
                }

                /**
                 * get record by record id
                 */
                public function getRecordByRecordId(recordId:int):Object {
                    _selectStatement = new SQLStatement();
                    _selectStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "SELECT RECORDID, DAY, CONTENT FROM RECORD ";
                    sql += "WHERE RECORDID = " + recordId;
		    trace(sql);
                    _selectStatement.text = sql;
                    var result:SQLResult = new SQLResult();
                    try {
                        _selectStatement.execute();
                        result = _selectStatement.getResult();
                    } catch(error:SQLError) {
                        trace("Error on getting record by record id.");
			trace(error.message);
			trace(error.details);
                    }
                    return (result.data[0] == null)? null : result.data[0];
                }

                /**
                 * delete user by username
                 */
                public function deleteUserByName(username:String):void {
                    _deleteStatement = new SQLStatement();
                    _deleteStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "DELETE FROM USER ";
                    sql += "WHERE USERNAME = '" + username + "'";
                    _deleteStatement.text = sql;
                    try { 
                        // execute the statement 
                        _deleteStatement.execute();
			trace("user delete by name success.");
                    } catch (error:SQLError) { 
                        trace("Error on deleting user by username.");
                    }
                }

                /**
                 * find all users
                 */
                public function findAllUser():Array {
                    _selectStatement = new SQLStatement();
                    _selectStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "SELECT USERID, USERNAME, PASSWORD, USERLEVEL, COMMENT FROM USER";
                    _selectStatement.text = sql;
                    var result:SQLResult = new SQLResult();
                    try {
                        _selectStatement.execute();
                        result = _selectStatement.getResult();
			trace("user getting success.");
                    } catch(error:SQLError) {
                        trace("Error on finding user.");
                    }
                    return result.data;
                }

                /**
                 * delete user
                 */
                public function deleteUser(userid:int):void {
                    _deleteStatement = new SQLStatement();
                    _deleteStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "DELETE FROM USER ";
                    sql += "WHERE USERID = " + userid;
                    _deleteStatement.text = sql;
                    try { 
                        // execute the statement 
                        _deleteStatement.execute();
			trace("user delete success.");
                    } catch (error:SQLError) { 
                        trace("Error on deleting user.");
                    }
                }

                /**
                 * update user
                 */
                public function updateUser(userid:int, username:String, pass:String, lev:int=0, cmt:String=null):void {
                    _updateStatement = new SQLStatement();
                    _updateStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "UPDATE USER SET ";
                    sql += "USERNAME = '" + username + "',";
                    sql += "PASSWORD = '" + pass + "',";
                    sql += "USERLEVEL = " + lev + ",";
                    sql += "COMMENT = '" + cmt + "'";
                    sql += "WHERE USERID = " + userid;
                    _updateStatement.text = sql;
                    try { 
                        // execute the statement 
                        _updateStatement.execute();
			trace("user update success.");
                    } catch (error:SQLError) { 
                        trace("Error on updating user.");
                    }
                }

                /**
		 * update buffer
		 */
		public function updateRecordWithBuffer(recordId:int, content:String):void {
		    recordIdBuffer = recordId;
		    recordConBuffer = content;
		    //updateRecordByRecordId(recordId, content);
		}
		public function saveRec():void {
		    updateRecordByRecordId(recordIdBuffer, recordConBuffer);
		}

                /**
		 * buffer
		 */
		private var recordConBuffer:String = "";
		private var recordIdBuffer:int = -1;

                /**
                 * update record
                 */
                private function updateRecordByRecordId(recordId:int, content:String):void {
                    _updateStatement = new SQLStatement();
                    _updateStatement.sqlConnection = _dbConnection;
                    var sql:String = "";
                    sql += "UPDATE RECORD SET ";
                    sql += "CONTENT = '" + content + "' ";
                    sql += "WHERE RECORDID = " + recordId;
                    _updateStatement.text = sql;
                    try { 
                        // execute the statement 
                        _updateStatement.execute();
			trace("record update success.");
                    } catch (error:SQLError) { 
                        trace("Error on updating record.");
                    }
                }
        }
}